{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SELECT from Nobel\n",
    "\n",
    "## `nobel` Nobel Laureates\n",
    "\n",
    "We continue practicing simple SQL queries on a single table.\n",
    "\n",
    "This tutorial is concerned with a table of Nobel prize winners:\n",
    "\n",
    "```\n",
    "nobel(yr, subject, winner)\n",
    "```\n",
    "\n",
    "Using the `SELECT` statement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Winners from 1950\n",
    "\n",
    "Change the query shown so that it displays Nobel prizes for 1950."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>yr</th>\n",
       "        <th>subject</th>\n",
       "        <th>winner</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950</td>\n",
       "        <td>Chemistry</td>\n",
       "        <td>Kurt Alder</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950</td>\n",
       "        <td>Chemistry</td>\n",
       "        <td>Otto Diels</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Bertrand Russell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950</td>\n",
       "        <td>Medicine</td>\n",
       "        <td>Philip S. Hench</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950</td>\n",
       "        <td>Medicine</td>\n",
       "        <td>Edward C. Kendall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950</td>\n",
       "        <td>Medicine</td>\n",
       "        <td>Tadeus Reichstein</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950</td>\n",
       "        <td>Peace</td>\n",
       "        <td>Ralph Bunche</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950</td>\n",
       "        <td>Physics</td>\n",
       "        <td>Cecil Powell</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1950, 'Chemistry', 'Kurt Alder'),\n",
       " (1950, 'Chemistry', 'Otto Diels'),\n",
       " (1950, 'Literature', 'Bertrand Russell'),\n",
       " (1950, 'Medicine', 'Philip S. Hench'),\n",
       " (1950, 'Medicine', 'Edward C. Kendall'),\n",
       " (1950, 'Medicine', 'Tadeus Reichstein'),\n",
       " (1950, 'Peace', 'Ralph Bunche'),\n",
       " (1950, 'Physics', 'Cecil Powell')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT yr, subject, winner FROM nobel\n",
    "WHERE yr = 1950;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. 1962 Literature\n",
    "\n",
    "Show who won the 1962 prize for Literature."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>winner</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Steinbeck</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('John Steinbeck',)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT winner FROM nobel\n",
    " WHERE yr = 1962 AND subject = 'Literature';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Albert Einstein\n",
    "\n",
    "Show the year and subject that won 'Albert Einstein' his prize."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>yr</th>\n",
       "        <th>subject</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1921</td>\n",
       "        <td>Physics</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1921, 'Physics')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT yr, subject FROM nobel\n",
    "WHERE winner = 'Albert Einstein';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Recent Peace Prizes\n",
    "\n",
    "Give the name of the 'Peace' winners since the year 2000, including 2000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "22 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>winner</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tunisian National Dialogue Quartet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kailash Satyarthi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Malala Yousafzai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>European Union</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ellen Johnson Sirleaf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Leymah Gbowee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tawakel Karman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Liu Xiaobo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Barack Obama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Martti Ahtisaari</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Intergovernmental Panel on Climate Change</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Al Gore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Grameen Bank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Muhammad Yunus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>International Atomic Energy Agency</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mohamed ElBaradei</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Wangari Maathai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shirin Ebadi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jimmy Carter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Nations</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">22 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Tunisian National Dialogue Quartet',),\n",
       " ('Kailash Satyarthi',),\n",
       " ('Malala Yousafzai',),\n",
       " ('European Union',),\n",
       " ('Ellen Johnson Sirleaf',),\n",
       " ('Leymah Gbowee',),\n",
       " ('Tawakel Karman',),\n",
       " ('Liu Xiaobo',),\n",
       " ('Barack Obama',),\n",
       " ('Martti Ahtisaari',),\n",
       " ('Intergovernmental Panel on Climate Change',),\n",
       " ('Al Gore',),\n",
       " ('Grameen Bank',),\n",
       " ('Muhammad Yunus',),\n",
       " ('International Atomic Energy Agency',),\n",
       " ('Mohamed ElBaradei',),\n",
       " ('Wangari Maathai',),\n",
       " ('Shirin Ebadi',),\n",
       " ('Jimmy Carter',),\n",
       " ('United Nations',),\n",
       " ('Kofi Annan',),\n",
       " ('Kim Dae-jung',)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT winner FROM nobel\n",
    "WHERE yr >= 2000 AND subject = 'Peace';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Literature in the 1980's\n",
    "\n",
    "Show all details **(yr, subject, winner)** of the Literature prize winners for 1980 to 1989 inclusive."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>yr</th>\n",
       "        <th>subject</th>\n",
       "        <th>winner</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1989</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Camilo José Cela</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1988</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Naguib Mahfouz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1987</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Joseph Brodsky</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1986</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Wole Soyinka</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1985</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Claude Simon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1984</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Jaroslav Seifert</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1983</td>\n",
       "        <td>Literature</td>\n",
       "        <td>William Golding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1982</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Gabriel García Márquez</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Elias Canetti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1980</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Czeslaw Milosz</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1989, 'Literature', 'Camilo José Cela'),\n",
       " (1988, 'Literature', 'Naguib Mahfouz'),\n",
       " (1987, 'Literature', 'Joseph Brodsky'),\n",
       " (1986, 'Literature', 'Wole Soyinka'),\n",
       " (1985, 'Literature', 'Claude Simon'),\n",
       " (1984, 'Literature', 'Jaroslav Seifert'),\n",
       " (1983, 'Literature', 'William Golding'),\n",
       " (1982, 'Literature', 'Gabriel García Márquez'),\n",
       " (1981, 'Literature', 'Elias Canetti'),\n",
       " (1980, 'Literature', 'Czeslaw Milosz')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM nobel\n",
    "WHERE subject = 'Literature' AND yr BETWEEN 1980 AND 1989;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Only Presidents\n",
    "\n",
    "Show all details of the presidential winners:\n",
    "\n",
    "- Theodore Roosevelt\n",
    "- Woodrow Wilson\n",
    "- Jimmy Carter\n",
    "- Barack Obama"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>yr</th>\n",
       "        <th>subject</th>\n",
       "        <th>winner</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2009</td>\n",
       "        <td>Peace</td>\n",
       "        <td>Barack Obama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2002</td>\n",
       "        <td>Peace</td>\n",
       "        <td>Jimmy Carter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1919</td>\n",
       "        <td>Peace</td>\n",
       "        <td>Woodrow Wilson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1906</td>\n",
       "        <td>Peace</td>\n",
       "        <td>Theodore Roosevelt</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2009, 'Peace', 'Barack Obama'),\n",
       " (2002, 'Peace', 'Jimmy Carter'),\n",
       " (1919, 'Peace', 'Woodrow Wilson'),\n",
       " (1906, 'Peace', 'Theodore Roosevelt')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM nobel\n",
    "WHERE winner IN ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter', 'Barack Obama');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. John\n",
    "\n",
    "Show the winners with first name John"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "26 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>winner</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John O&#x27;Keefe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John B. Gurdon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John C. Mather</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John L. Hall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John B. Fenn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John E. Sulston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Pople</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Hume</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John E. Walker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John C. Harsanyi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John F. Nash Jr.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John C. Polanyi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John R. Vane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John H. van Vleck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Cornforth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John R. Hicks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Bardeen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John C. Kendrew</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Steinbeck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Bardeen</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">26 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[(\"John O'Keefe\",),\n",
       " ('John B. Gurdon',),\n",
       " ('John C. Mather',),\n",
       " ('John L. Hall',),\n",
       " ('John B. Fenn',),\n",
       " ('John E. Sulston',),\n",
       " ('John Pople',),\n",
       " ('John Hume',),\n",
       " ('John E. Walker',),\n",
       " ('John C. Harsanyi',),\n",
       " ('John F. Nash Jr.',),\n",
       " ('John C. Polanyi',),\n",
       " ('John R. Vane',),\n",
       " ('John H. van Vleck',),\n",
       " ('John Cornforth',),\n",
       " ('John R. Hicks',),\n",
       " ('John Bardeen',),\n",
       " ('John C. Kendrew',),\n",
       " ('John Steinbeck',),\n",
       " ('John Bardeen',),\n",
       " ('John F. Enders',),\n",
       " ('John Cockcroft',),\n",
       " ('John H. Northrop',),\n",
       " ('John R. Mott',),\n",
       " ('John Galsworthy',),\n",
       " ('John Macleod',)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT winner FROM nobel\n",
    "WHERE winner LIKE 'John%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Chemistry and Physics from different years\n",
    "\n",
    "**Show the year, subject, and name of Physics winners for 1980 together with the Chemistry winners for 1984.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>yr</th>\n",
       "        <th>subject</th>\n",
       "        <th>winner</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1984</td>\n",
       "        <td>Chemistry</td>\n",
       "        <td>Bruce Merrifield</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1980</td>\n",
       "        <td>Physics</td>\n",
       "        <td>James Cronin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1980</td>\n",
       "        <td>Physics</td>\n",
       "        <td>Val Fitch</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1984, 'Chemistry', 'Bruce Merrifield'),\n",
       " (1980, 'Physics', 'James Cronin'),\n",
       " (1980, 'Physics', 'Val Fitch')]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT yr, subject, winner FROM nobel\n",
    "WHERE (yr = 1980 AND subject = 'Physics') OR (yr = 1984 AND subject = 'Chemistry');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Exclude Chemists and Medics\n",
    "\n",
    "**Show the year, subject, and name of winners for 1980 excluding Chemistry and Medicine**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>yr</th>\n",
       "        <th>subject</th>\n",
       "        <th>winner</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1980</td>\n",
       "        <td>Economics</td>\n",
       "        <td>Lawrence R. Klein</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1980</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Czeslaw Milosz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1980</td>\n",
       "        <td>Peace</td>\n",
       "        <td>Adolfo Pérez Esquivel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1980</td>\n",
       "        <td>Physics</td>\n",
       "        <td>James Cronin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1980</td>\n",
       "        <td>Physics</td>\n",
       "        <td>Val Fitch</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1980, 'Economics', 'Lawrence R. Klein'),\n",
       " (1980, 'Literature', 'Czeslaw Milosz'),\n",
       " (1980, 'Peace', 'Adolfo Pérez Esquivel'),\n",
       " (1980, 'Physics', 'James Cronin'),\n",
       " (1980, 'Physics', 'Val Fitch')]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT yr, subject, winner FROM nobel\n",
    "WHERE yr = 1980 AND subject NOT IN ('Chemistry', 'Medicine');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Early Medicine, Late Literature\n",
    "\n",
    "Show year, subject, and name of people who won a 'Medicine' prize in an early year (before 1910, not including 1910) together with winners of a 'Literature' prize in a later year (after 2004, including 2004)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "23 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>yr</th>\n",
       "        <th>subject</th>\n",
       "        <th>winner</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2015</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Svetlana Alexievich</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2014</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Patrick Modiano</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Alice Munro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2012</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Mo Yan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2011</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Tomas Tranströmer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2010</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Mario Vargas Llosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2009</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Herta Müller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2008</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Jean-Marie Gustave Le Clézio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2007</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Doris Lessing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2006</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Orhan Pamuk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2005</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Harold Pinter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2004</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Elfriede Jelinek</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1909</td>\n",
       "        <td>Medicine</td>\n",
       "        <td>Theodor Kocher</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1908</td>\n",
       "        <td>Medicine</td>\n",
       "        <td>Paul Ehrlich</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1908</td>\n",
       "        <td>Medicine</td>\n",
       "        <td>Ilya Mechnikov</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1907</td>\n",
       "        <td>Medicine</td>\n",
       "        <td>Alphonse Laveran</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1906</td>\n",
       "        <td>Medicine</td>\n",
       "        <td>Camillo Golgi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1906</td>\n",
       "        <td>Medicine</td>\n",
       "        <td>Santiago Ramón y Cajal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1905</td>\n",
       "        <td>Medicine</td>\n",
       "        <td>Robert Koch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1904</td>\n",
       "        <td>Medicine</td>\n",
       "        <td>Ivan Pavlov</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">23 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[(2015, 'Literature', 'Svetlana Alexievich'),\n",
       " (2014, 'Literature', 'Patrick Modiano'),\n",
       " (2013, 'Literature', 'Alice Munro'),\n",
       " (2012, 'Literature', 'Mo Yan'),\n",
       " (2011, 'Literature', 'Tomas Tranströmer'),\n",
       " (2010, 'Literature', 'Mario Vargas Llosa'),\n",
       " (2009, 'Literature', 'Herta Müller'),\n",
       " (2008, 'Literature', 'Jean-Marie Gustave Le Clézio'),\n",
       " (2007, 'Literature', 'Doris Lessing'),\n",
       " (2006, 'Literature', 'Orhan Pamuk'),\n",
       " (2005, 'Literature', 'Harold Pinter'),\n",
       " (2004, 'Literature', 'Elfriede Jelinek'),\n",
       " (1909, 'Medicine', 'Theodor Kocher'),\n",
       " (1908, 'Medicine', 'Paul Ehrlich'),\n",
       " (1908, 'Medicine', 'Ilya Mechnikov'),\n",
       " (1907, 'Medicine', 'Alphonse Laveran'),\n",
       " (1906, 'Medicine', 'Camillo Golgi'),\n",
       " (1906, 'Medicine', 'Santiago Ramón y Cajal'),\n",
       " (1905, 'Medicine', 'Robert Koch'),\n",
       " (1904, 'Medicine', 'Ivan Pavlov'),\n",
       " (1903, 'Medicine', 'Niels Ryberg Finsen'),\n",
       " (1902, 'Medicine', 'Ronald Ross'),\n",
       " (1901, 'Medicine', 'Emil von Behring')]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT yr, subject, winner FROM nobel\n",
    "WHERE (subject = 'Medicine' AND yr < 1910) OR (subject = 'Literature' AND yr >= 2004);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11. Umlaut\n",
    "\n",
    "Find all details of the prize won by PETER GRÜNBERG\n",
    "\n",
    "> _Non-ASCII characters_   \n",
    "> The u in his name has an umlaut. You may find this link useful <https://en.wikipedia.org/wiki/%C3%9C#Keyboarding>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>yr</th>\n",
       "        <th>subject</th>\n",
       "        <th>winner</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2007</td>\n",
       "        <td>Physics</td>\n",
       "        <td>Peter Grünberg</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2007, 'Physics', 'Peter Grünberg')]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- SET NAMES 'utf8';\n",
    "SELECT * FROM nobel WHERE winner='Peter Grünberg';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12. Apostrophe\n",
    "\n",
    "Find all details of the prize won by EUGENE O'NEILL\n",
    "\n",
    "> _Escaping single quotes_   \n",
    "> You can't put a single quote in a quote string directly. You can use two single quotes within a quoted string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>yr</th>\n",
       "        <th>subject</th>\n",
       "        <th>winner</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1936</td>\n",
       "        <td>Literature</td>\n",
       "        <td>Eugene O&#x27;Neill</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1936, 'Literature', \"Eugene O'Neill\")]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM nobel\n",
    "WHERE winner = 'Eugene O''Neill';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13. Knights of the realm\n",
    "\n",
    "Knights in order\n",
    "\n",
    "**List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "21 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>winner</th>\n",
       "        <th>yr</th>\n",
       "        <th>subject</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Martin J. Evans</td>\n",
       "        <td>2007</td>\n",
       "        <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Peter Mansfield</td>\n",
       "        <td>2003</td>\n",
       "        <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Paul Nurse</td>\n",
       "        <td>2001</td>\n",
       "        <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Harold Kroto</td>\n",
       "        <td>1996</td>\n",
       "        <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir James W. Black</td>\n",
       "        <td>1988</td>\n",
       "        <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Arthur Lewis</td>\n",
       "        <td>1979</td>\n",
       "        <td>Economics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Nevill F. Mott</td>\n",
       "        <td>1977</td>\n",
       "        <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Bernard Katz</td>\n",
       "        <td>1970</td>\n",
       "        <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir John Eccles</td>\n",
       "        <td>1963</td>\n",
       "        <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Frank Macfarlane Burnet</td>\n",
       "        <td>1960</td>\n",
       "        <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Cyril Hinshelwood</td>\n",
       "        <td>1956</td>\n",
       "        <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Robert Robinson</td>\n",
       "        <td>1947</td>\n",
       "        <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Alexander Fleming</td>\n",
       "        <td>1945</td>\n",
       "        <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Howard Florey</td>\n",
       "        <td>1945</td>\n",
       "        <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Henry Dale</td>\n",
       "        <td>1936</td>\n",
       "        <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Norman Angell</td>\n",
       "        <td>1933</td>\n",
       "        <td>Peace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Charles Sherrington</td>\n",
       "        <td>1932</td>\n",
       "        <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Venkata Raman</td>\n",
       "        <td>1930</td>\n",
       "        <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Frederick Hopkins</td>\n",
       "        <td>1929</td>\n",
       "        <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Austen Chamberlain</td>\n",
       "        <td>1925</td>\n",
       "        <td>Peace</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">21 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Sir Martin J. Evans', 2007, 'Medicine'),\n",
       " ('Sir Peter Mansfield', 2003, 'Medicine'),\n",
       " ('Sir Paul Nurse', 2001, 'Medicine'),\n",
       " ('Sir Harold Kroto', 1996, 'Chemistry'),\n",
       " ('Sir James W. Black', 1988, 'Medicine'),\n",
       " ('Sir Arthur Lewis', 1979, 'Economics'),\n",
       " ('Sir Nevill F. Mott', 1977, 'Physics'),\n",
       " ('Sir Bernard Katz', 1970, 'Medicine'),\n",
       " ('Sir John Eccles', 1963, 'Medicine'),\n",
       " ('Sir Frank Macfarlane Burnet', 1960, 'Medicine'),\n",
       " ('Sir Cyril Hinshelwood', 1956, 'Chemistry'),\n",
       " ('Sir Robert Robinson', 1947, 'Chemistry'),\n",
       " ('Sir Alexander Fleming', 1945, 'Medicine'),\n",
       " ('Sir Howard Florey', 1945, 'Medicine'),\n",
       " ('Sir Henry Dale', 1936, 'Medicine'),\n",
       " ('Sir Norman Angell', 1933, 'Peace'),\n",
       " ('Sir Charles Sherrington', 1932, 'Medicine'),\n",
       " ('Sir Venkata Raman', 1930, 'Physics'),\n",
       " ('Sir Frederick Hopkins', 1929, 'Medicine'),\n",
       " ('Sir Austen Chamberlain', 1925, 'Peace'),\n",
       " ('Sir William Ramsay', 1904, 'Chemistry')]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT winner, yr, subject FROM nobel\n",
    "WHERE winner LIKE 'Sir%'\n",
    "ORDER BY yr DESC, winner;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14. Chemistry and Physics last\n",
    "\n",
    "The expression **subject IN ('Chemistry','Physics')** can be used as a value - it will be 0 or 1.\n",
    "\n",
    "**Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "9 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>winner</th>\n",
       "        <th>subject</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Richard Stone</td>\n",
       "        <td>Economics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jaroslav Seifert</td>\n",
       "        <td>Literature</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>César Milstein</td>\n",
       "        <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Georges J.F. Köhler</td>\n",
       "        <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Niels K. Jerne</td>\n",
       "        <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Desmond Tutu</td>\n",
       "        <td>Peace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bruce Merrifield</td>\n",
       "        <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Carlo Rubbia</td>\n",
       "        <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Simon van der Meer</td>\n",
       "        <td>Physics</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Richard Stone', 'Economics'),\n",
       " ('Jaroslav Seifert', 'Literature'),\n",
       " ('César Milstein', 'Medicine'),\n",
       " ('Georges J.F. Köhler', 'Medicine'),\n",
       " ('Niels K. Jerne', 'Medicine'),\n",
       " ('Desmond Tutu', 'Peace'),\n",
       " ('Bruce Merrifield', 'Chemistry'),\n",
       " ('Carlo Rubbia', 'Physics'),\n",
       " ('Simon van der Meer', 'Physics')]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT winner, subject FROM nobel\n",
    "WHERE yr=1984\n",
    "ORDER BY subject IN ('Physics','Chemistry'), subject, winner;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
